#!bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

DWS_HOUR_COMMON_ADS_AGG="
INSERT OVERWRITE TABLE jtp_ads_warehouse.dws_hour_common_ads_agg PARTITION(dt='${data_date}')
SELECT
    hour(from_unixtime(event_time/1000)) AS hr,
    ad_id,
    ads_name,
    platform_id,
    platform_name_zh,
    client_province,
    client_city,
    client_os_type,
    client_browser_type,
    is_invalid_traffic,
    count(if(event_type='click',ad_id,NULL)) AS click_count,
    count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY hour(from_unixtime(event_time/1000)),
    ad_id,
    ads_name,
    platform_id,
    platform_name_zh,
    client_province,
    client_city,
    client_os_type,
    client_browser_type,
    is_invalid_traffic;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DWS_HOUR_COMMON_ADS_AGG}"